#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb
import utlis
import database_gushiwen


def openDatabase():
    """
    打开古诗文的数据库连接
    :return:
    """
    db = MySQLdb.connect("localhost", "root", "root", "gushici", charset='utf8')
    return db


def insert_author(db, author):
    """
    插入类型为Author的作者数据
    :param db:
    :param author:
    :return:
    """
    ishave = have_author_id(db, author.id)
    if ishave:
        print("have this author " + str(author.id))
        return
    cursor = db.cursor()
    sql = "INSERT INTO AUTHOR(id,likes, pic, idnew, nameStr, chaodai, creTime, shiCount, cont, ipStr) \
             VALUES ('%d', '%d', '%s','%s','%s','%s','%s','%d', '%s', '%s')" % \
          (author.id, author.likes, author.pic, author.idnew, author.nameStr, author.chaodai, author.creTime,
           author.shiCount, author.cont, author.ipStr)
    print(sql)
    cursor.execute(sql)
    db.commit()


def insert_gushiwen(db, model):
    ishave = have_gushiwen_id(db, model.id)
    if ishave:
        return
    cursor = db.cursor()
    sql = "INSERT INTO gushiwen(id, idnew, nameStr, author, chaodai, cont, axing, bxing, cxing, dxing, exing, type, tag, " \
          "langsongAuthor, langsongAuthorPY, yizhuAuthor, yizhuCankao, yizhuYuanchuang, yizhuIspass, shangIspass, " \
          "beijingIspass, pinglunCount) VALUES ('%d', '%s', '%s','%s','%s','%s','%d','%d', '%d', '%d', '%d', '%s', " \
          "'%s', '%s', '%s', '%s', '%s', '%r', '%r', '%r', '%r', '%d')" % \
          (model.id, model.idnew, model.nameStr, model.author, model.chaodai, model.cont,
           model.axing,
           model.bxing, model.cxing, model.dxing, model.exing, model.type, model.tag,
           model.langsongAuthor,
           model.langsongAuthorPY, model.yizhuAuthor, model.yizhuCankao,
           int(model.yizhuYuanchuang), int(model.yizhuIspass),
           int(model.shangIspass), int(model.beijingIspass), model.pinglunCount)
    print(sql)
    cursor.execute(sql)
    db.commit()


def insert_author_ziliao(db, model):
    ishave = have_author_ziliao_id(db, model.id)
    if ishave:
        return
    cursor = db.cursor()
    sql = "INSERT INTO author_ziliao(id, nameStr, author, authorID, ok, noOk, cont, isPass, ipStr, creTime, cankao, isYuanchuang) \
            VALUES ('%d', '%s', '%s','%d','%d','%d','%s','%d', '%s', '%s', '%s', '%d')" % \
          (model.id, model.nameStr, model.author, model.authorID, model.ok, model.noOk, model.cont, int(model.isPass),
           model.ipStr, model.creTime, model.cankao, int(model.isYuanchuang))
    print(sql)
    cursor.execute(sql)
    db.commit()


def insert_mingju(db, model):
    """
    插入名句
    :param db:
    :param model:
    :return:
    """
    ishave = have_mingju_id(db, model.id)
    if ishave:
        update_mingju(db, model)
        return
    cursor = db.cursor()
    sql = "INSERT INTO mingju(id, idnew, nameStr, classStr, type, shiID, shiIDnew, exing, author, shiName, ipStr, isShiwen, gujiyiwen, zhangjieID, zhangjieNameStr, themeStr, classifyStr) \
            VALUES ('%d', '%s', '%s','%s','%s','%d','%s','%d', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s')" % \
          (model.id, model.idnew, model.nameStr, model.classStr, model.type, model.shiID, model.shiIDnew, model.exing,
           model.author, model.shiName, model.ipStr, int(model.isShiwen), model.gujiyiwen, model.zhangjieID, model.zhangjieNameStr,
           model.themeStr, model.classifyStr)
    print(sql)
    cursor.execute(sql)
    db.commit()


def update_mingju(db, model):
    """
    更新名句的分类和主题
    :param db:
    :param model:
    :return:
    """
    cursor = db.cursor()
    curthemeclassifyStr_sql = "SELECT themeStr,classifyStr FROM mingju WHERE id=%d" % model.id
    cursor.execute(curthemeclassifyStr_sql)
    curthemecurthemeclassifyStr = cursor.fetchall()
    curtheme = utlis.toString(curthemecurthemeclassifyStr[0][0])
    curclassify = utlis.toString(curthemecurthemeclassifyStr[0][1])
    themeflag = False
    classifyflag = False
    if curtheme is not None:
        if curtheme.find(model.themeStr) != -1:
            curtheme = curtheme.strip()
            themeflag = True
            curthemeList = set(curtheme.split('|'))
            curthemeList.add(model.themeStr)
            delimiter = '|'
            model.themeStr = delimiter.join(curthemeList)
    if curclassify is not None:
        if curclassify.find(model.classifyStr) != -1:
            classifyflag = True
            curclassify = curclassify.strip()
            curclassifyList = set(curclassify.split('|'))
            curclassifyList.add(model.classifyStr)
            delimiter = '|'
            model.classifyStr = delimiter.join(curclassifyList)
    if themeflag and classifyflag:
        sql = "UPDATE mingju SET themeStr='%s', classifyStr='%s' WHERE id=%d" % (model.themeStr, model.classifyStr, model.id)
        print(sql)
        cursor.execute(sql)
        db.commit()


def insert_fanyi(db, model):
    """
    插入古诗文翻译
    :param db:
    :param model:
    :return:
    """
    ishave = have_fanyi_id(db, model.id)
    if ishave:
        return
    cursor = db.cursor()
    sql = "INSERT INTO gswfanyi(id, nameStr, author, shiID, shiIDnew, ok, noOk, cont, cankao, isYuanchuang) \
            VALUES ('%d', '%s', '%s','%d','%s','%d','%d', '%s', '%s', '%d')" % \
          (model.id, model.nameStr, model.author, model.shiID, model.shiIDnew, model.ok, model.noOk, model.cont, model.cankao, int(model.isYuanchuang))
    print(sql)
    cursor.execute(sql)
    db.commit()


def insert_shangxi(db, model):
    """
    插入古诗文赏析
    :param db:
    :param model:
    :return:
    """
    ishave = have_shangxi_id(db, model.id)
    if ishave:
        return
    cursor = db.cursor()
    sql = "INSERT INTO gswshangxi(id, nameStr, author, shiID, shiIDnew, ok, noOk, cont, cankao, isYuanchuang) \
            VALUES ('%d', '%s', '%s','%d','%s','%d','%d', '%s', '%s', '%d')" % \
          (model.id, model.nameStr, model.author, model.shiID, model.shiIDnew, model.ok, model.noOk, model.cont, model.cankao, int(model.isYuanchuang))
    print(sql)
    cursor.execute(sql)
    db.commit()


def insert_gswyi(db, model):
    """
    插入古诗文译文
    :param db:
    :param model:
    :return:
    """
    if len(model.cont.strip()) == 0:
        return
    ishave = have_gsw_yi(db, model.id)
    if ishave:
        return
    cursor = db.cursor()
    sql = "INSERT INTO %s(id, cont, cankao) VALUES ('%d', '%s', '%s')" % (database_gushiwen.GSWYI, model.id, model.cont, model.cankao)
    print(sql)
    cursor.execute(sql)
    db.commit()


def insert_gswzhu(db, model):
    """
    插入古诗文注释
    :param db:
    :param model:
    :return:
    """
    if len(model.cont.strip()) == 0:
        return
    ishave = have_gsw_zhu(db, model.id)
    if ishave:
        return
    cursor = db.cursor()
    sql = "INSERT INTO %s(id, cont, cankao) VALUES ('%d', '%s', '%s')" % (database_gushiwen.GSWZHU, model.id, model.cont, model.cankao)
    print(sql)
    cursor.execute(sql)
    db.commit()


def insert_gswyizhu(db, model):
    """
    插入古诗文翻译和注释
    :param db:
    :param model:
    :return:
    """
    if len(model.cont.strip()) == 0:
        return
    ishave = have_gsw_yizhu(db, model.id)
    if ishave:
        return
    cursor = db.cursor()
    sql = "INSERT INTO %s(id, cont, cankao) VALUES ('%d', '%s', '%s')" % (database_gushiwen.GSWYIZHU, model.id, model.cont, model.cankao)
    print(sql)
    cursor.execute(sql)
    db.commit()


def insert_gswshang(db, model):
    """
    插入古诗文翻译和【赏】
    :param db:
    :param model:
    :return:
    """
    if len(model.cont.strip()) == 0:
        return
    ishave = have_gsw_shang(db, model.id)
    if ishave:
        return
    cursor = db.cursor()
    sql = "INSERT INTO %s(id, cont, cankao) VALUES ('%d', '%s', '%s')" % (database_gushiwen.GSWSHANG, model.id, model.cont, model.cankao)
    print(sql)
    cursor.execute(sql)
    db.commit()


def have_gsw_yi(db, id):
    """
    根据id,判断古诗文【译】是否已经写入数据库
    :param db:
    :param id:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT COUNT(*) FROM %s WHERE id=%d" % (database_gushiwen.GSWYI, id)
    curosr.execute(sql)
    count = curosr.fetchall()
    return int(count[0][0]) > 0


def have_gsw_zhu(db, id):
    """
    根据id,判断古诗文【注】是否已经写入数据库
    :param db:
    :param id:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT COUNT(*) FROM %s WHERE id=%d" % (database_gushiwen.GSWZHU, id)
    curosr.execute(sql)
    count = curosr.fetchall()
    return int(count[0][0]) > 0


def have_gsw_yizhu(db, id):
    """
    根据id,判断古诗文【译、注】是否已经写入数据库
    :param db:
    :param id:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT COUNT(*) FROM %s WHERE id=%d" % (database_gushiwen.GSWYIZHU, id)
    curosr.execute(sql)
    count = curosr.fetchall()
    return int(count[0][0]) > 0


def have_gsw_shang(db, id):
    """
    根据id,判断古诗文【赏】是否已经写入数据库
    :param db:
    :param id:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT COUNT(*) FROM %s WHERE id=%d" % (database_gushiwen.GSWSHANG, id)
    curosr.execute(sql)
    count = curosr.fetchall()
    return int(count[0][0]) > 0


def have_author_id(db, author_id):
    """
    根据作者id，判断数据库是否已经录入该作者数据
    :param db:
    :param author_id:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT COUNT(*) FROM author WHERE id=%d" % author_id
    curosr.execute(sql)
    count = curosr.fetchall()
    return int(count[0][0]) > 0


def have_gushiwen_id(db, gushici_id):
    """
    根据古诗文id，判断是否已经录入该古诗文
    :param db:
    :param gushici_id:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT COUNT(*) FROM gushiwen WHERE id=%d" % gushici_id
    curosr.execute(sql)
    count = curosr.fetchall()
    return int(count[0][0]) > 0


def have_gushiwen_author(db, author_name):
    """
    根据作者的姓名找到是否有该作者的古诗文
    :param db:
    :param author_name:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT COUNT(*) FROM gushiwen WHERE author='%s'" % author_name
    curosr.execute(sql)
    count = curosr.fetchall()
    return int(count[0][0]) > 0


def have_author_ziliao_id(db, id):
    """
    根据作者的id找到是否有该作者的资料
    :param db:
    :param id:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT COUNT(*) FROM author_ziliao WHERE id=%d" % id
    curosr.execute(sql)
    count = curosr.fetchall()
    return int(count[0][0]) > 0


def get_author_by_id(db, id, key):
    """
    根据作者的id找到是否有该作者的资料
    :param db:
    :param id:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT %s FROM %s WHERE id=%d" % (key, database_gushiwen.AUTHOR, id)
    curosr.execute(sql)
    result = curosr.fetchall()
    return result


def have_mingju_id(db, id):
    """
    根据id查找时候有该id对应的名句
    :param db:
    :param id:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT COUNT(*) FROM mingju WHERE id=%d" % id
    curosr.execute(sql)
    count = curosr.fetchall()
    return int(count[0][0]) > 0


def have_fanyi_id(db, id):
    """
    根据id查找时候有该id对应的翻译
    :param db:
    :param id:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT COUNT(*) FROM %s WHERE id=%d" % (database_gushiwen.GSWFANYI, id)
    curosr.execute(sql)
    count = curosr.fetchall()
    return int(count[0][0]) > 0


def have_shangxi_id(db, id):
    """
    根据id查找时候有该id对应的翻译
    :param db:
    :param id:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT COUNT(*) FROM %s WHERE id=%d" % (database_gushiwen.GSWSHANGXI, id)
    curosr.execute(sql)
    count = curosr.fetchall()
    return int(count[0][0]) > 0


def get_author_name(idnew, db):
    """
    根据idnew获取作者的姓名
    :param idnew:
    :param db:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT nameStr FROM author WHERE idnew='%s'" % idnew
    print(sql)
    curosr.execute(sql)
    result = curosr.fetchall()
    return result


def get_author_id(key, db):
    """
    获取作者的key字段信息
    :param key:
    :param db:
    :return:
    """
    cursor = db.cursor()
    sql = "SELECT %s FROM %s" % (key, database_gushiwen.AUTHOR)
    print(sql)
    cursor.execute(sql)
    return cursor.fetchall()


def get_author_where(key, where, db):
    """
    获取作者的key字段信息
    :param key:
    :param db:
    :return:
    """
    cursor = db.cursor()
    sql = "SELECT %s FROM %s WHERE %s" % (key, database_gushiwen.AUTHOR, where)
    print(sql)
    cursor.execute(sql)
    return cursor.fetchall()


def get_gws_id(key, db):
    """
    获取古诗文的key字段信息
    :param key:
    :param db:
    :return:
    """
    cursor = db.cursor()
    sql = "SELECT %s FROM %s" % (key, database_gushiwen.GUSHIWEN)
    print(sql)
    cursor.execute(sql)
    return cursor.fetchall()


def get_theme(db):
    """
    获取主题类别
    :param db:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT name FROM theme"
    print(sql)
    curosr.execute(sql)
    result = curosr.fetchall()
    return result


def get_classify(db):
    """
    获取分类类别
    :param db:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT name FROM classify"
    print(sql)
    curosr.execute(sql)
    result = curosr.fetchall()
    return result


def get_gushiwen_p(db, key, p, size=50):
    """
    古诗文分页查询
    :param db:
    :param key:
    :param p:
    :param size:
    :return:
    """
    curosr = db.cursor()
    sql = "SELECT %s FROM %s WHERE _id > %d ORDER BY _id LIMIT %d" % (key, database_gushiwen.GUSHIWEN, p *size, size)
    print(sql)
    curosr.execute(sql)
    result = curosr.fetchall()
    return result


def get_gushiwen_where(key, where, db):
    """
    获取作者的key字段信息
    :param key:
    :param db:
    :return:
    """
    cursor = db.cursor()
    sql = "SELECT %s FROM %s WHERE %s" % (key, database_gushiwen.GUSHIWEN, where)
    print(sql)
    cursor.execute(sql)
    return cursor.fetchall()